package project.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import com.mysql.jdbc.CallableStatement;

import project.model.PrComment;
import project.model.PrComposition;
import project.model.PrProject;
import project.model.PrTestProject;

import models.util.MySqlDataAccessHelper;



public class pr_projectDAO {
	MySqlDataAccessHelper hep=new MySqlDataAccessHelper();
	PreparedStatement pr=null;
	DateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

	private ArrayList<PrProject> listproject_tc=null;
	private ArrayList<PrComposition> loadAllTesterDAO_tc=null;
	private ArrayList<PrTestProject> loadAllListErrorDAO_tc=null;
	private ArrayList<PrComment> loadAllCommentDAO_tc=null;
	private String loadManagerNameByID_tc=null;
	private Date loadDateStartByID_tc=null;
	private int positionEmployee=1;
	

	public void deleteItemSelected_Error(String databasename,String idtest) throws SQLException {
        try {
    		hep.open(databasename);
    		String sql = "DELETE FROM pr_testproject WHERE IDTest = '"+idtest+"'";
            hep.executeUpdate(sql);           
        } catch (Exception ex){
//            ex.printStackTrace(System.out);
        }finally {
        	hep.close();
        }
	}
	
	public void addNewCommentErrorDAO_tc(String databasename,String idpro,String idempl,String title,String namecolor,String content){
		try{
			hep.open(databasename);
			DateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	        String id = new Date().getTime() + "";			
	        title += "-%$#@!-" + namecolor;
			
			String sql = "insert into pr_testproject VALUES(?,?,?,?,?,?,?,?)";
			pr=hep.getConnection().prepareStatement(sql);
			pr.setString(1, id);
            pr.setString(2, idpro);
            pr.setString(3, idempl);
            pr.setString(4, title);
            pr.setString(5, content);
            pr.setString(6, format.format(new Date()));
            pr.setInt(7, 0);
            pr.setInt(8, 0);
            pr.executeUpdate();
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		finally{
			hep.close();
		}
	}

	public int getPositionEmployee(String databasename,String id,String idpro ) {
		try{
			hep.open(databasename);
			String sql="select Position from pr_composition where IDEmployee='"+id+"' and IDProject='"+idpro+"'";
//			pr=hep.getConnection().prepareStatement(sql);
//			pr.setString(1, id);
//			pr.setString(2, idpro);
			ResultSet rs=hep.executeQuery(sql);
			while(rs.next()){
				positionEmployee=rs.getInt("Position");
			}
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		finally{
			hep.close();
		}
		return positionEmployee;
	}

	public Date getLoadDateStartByID_tc(String databasename,String id) {
		try{
			hep.open(databasename);
			String sql="select DateStart from pr_project where IDProject='"+id+"'";
//			pr=hep.getConnection().prepareStatement(sql);
//			pr.setString(1, id);
			ResultSet rs=hep.executeQuery(sql);
			while(rs.next()){
				loadDateStartByID_tc=rs.getDate("DateStart");
			}
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		finally{
			hep.close();
		}
		return loadDateStartByID_tc;
	}

	public String getLoadManagerNameByID_tc(String databasename,String id) {
		try{
			hep.open(databasename);
			String sql="select FullName from employee where IDEmployee='"+id+"'";
//			pr=hep.getConnection().prepareStatement(sql);
//			pr.setString(1, id);
			ResultSet rs=hep.executeQuery(sql);
			while(rs.next()){
				loadManagerNameByID_tc=rs.getString("FullName");
			}
			rs.close();
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		finally{
			hep.close();
		}
		return loadManagerNameByID_tc;
	}

	public void deleteItemComment(String databasename,String comment){
		try{
			hep.open(databasename);
			String sql="delete from pr_comment where IDComment='"+comment+"'";
			hep.executeUpdate(sql);
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		finally{
			hep.close();
		}
	}

	public ArrayList<PrComment> getLoadAllCommentDAO_tc(String databasename,String idtest) {
		loadAllCommentDAO_tc=new ArrayList<PrComment>();
		try{
			hep.open(databasename);
			String sql = "call sp_getCommentWithIDTest(?)";
			CallableStatement cs=(CallableStatement) hep.getConnection().prepareCall(sql);
			cs.setString(1, idtest);
			ResultSet rs=cs.executeQuery();
			while(rs.next()){       
				PrComment prCo=new PrComment(rs.getString("IDComment"),rs.getString("IDTestProject"),rs.getString("IDEmployee"),rs.getString("Comment"),
						rs.getTimestamp("DateComment"),rs.getString("FullName"));
				prCo.setEmployeeName_(rs.getString("FullName"));
				loadAllCommentDAO_tc.add(prCo);       	
            }
			rs.close();
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		finally{
			hep.close();
		}
		return loadAllCommentDAO_tc;
	}

	public ArrayList<PrTestProject> getLoadAllListErrorDAO(String databasename,String idproject) {
		loadAllListErrorDAO_tc=new ArrayList<PrTestProject>();
		try{
			hep.open(databasename);
			String sql="";
			sql = "SELECT tp.IDTest, tp.IDProject, tp.IDEmployee, tp.Title, tp.TextTest, tp.Time,e.FullName,e.IDEmployee "+
  				  " FROM pr_testproject tp, employee e WHERE tp.IDProject = '"+idproject+"' AND e.IDEmployee = tp.IDEmployee ORDER BY IDTest DESC";
			ResultSet rs= hep.executeQuery(sql);
			while(rs.next()){ 
//				PrTestProject prTest=new PrTestProject();
//				prTest.setIdTest(rs.getString("IDTest"));
//				prTest.setIdProject(rs.getString("IDProject"));
//				prTest.setTitle(rs.getString("Title"));
//				prTest.setText(rs.getString("TextTest"));
//				prTest.setTime(rs.getTimestamp("Time"));
//				prTest.setStatus(rs.getInt("Status"));
//				prTest.setDelete(rs.getInt("Delete"));
//				prTest.setNameEmployee(rs.getString("FullName"));
//				prTest.setDb(databasename);
//				loadAllListErrorDAO_tc.add(prTest);
				PrTestProject prTest=new PrTestProject(rs.getString("IDTest"), rs.getString("IDProject"), rs.getString("Title"),
						rs.getString("TextTest"), rs.getTimestamp("Time"), rs.getString("FullName"));
				prTest.setDb(databasename);
				prTest.setIdmanager(rs.getString("IDEmployee"));
				prTest.setEmployeeName(rs.getString("FullName"));
				loadAllListErrorDAO_tc.add(prTest);
            }
			rs.close();
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		finally{
			hep.close();
		}
		return loadAllListErrorDAO_tc;
	}
	
	

	public ArrayList<PrComposition> getLoadAllTesterDAO_tc(String databasename,String idproject,int position) {
		loadAllTesterDAO_tc=new ArrayList<PrComposition>();
		try{
			hep.open(databasename);
			String sql="SELECT c.IDComposition, e.FullName FROM pr_composition c, employee e " +
		        		" WHERE c.IDProject = '"+idproject+"' AND c.IDEmployee = e.IDEmployee and c.Position="+position;
			ResultSet rs= hep.executeQuery(sql);
			while(rs.next()){
				loadAllTesterDAO_tc.add(new PrComposition(rs.getString("IDComposition"), rs.getString("FullName")));
			}
			rs.close();
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		finally{
			hep.close();
		}
		return loadAllTesterDAO_tc;
	}

	public ArrayList<PrProject> getListproject_tc(String databasename,String idEmployee) throws SQLException {
		listproject_tc=new ArrayList<PrProject>();
		try{
			hep.open(databasename);
			String sql="select p.IDProject,p.NameProject,p.Description, p.DateStart,p.IDManager,p.DateComplete,p.Status,e.FullName " +
					"from pr_project p,pr_composition c, employee e " +
					"where (p.IDProject = c.IDProject and e.IDEmployee = c.IDEmployee and c.IDEmployee ='"+idEmployee+"') " +
					"or (p.IDProject = '"+idEmployee+"' and p.IDProject = e.IDEmployee) GROUP BY p.IDProject ORDER BY p.NameProject ASC";
					ResultSet rs = hep.executeQuery(sql);
					while(rs.next()){
						PrProject prPro = new PrProject(rs.getString("IDProject"), rs.getString("NameProject"), rs.getString("Description"), rs.getString("IDManager"),
			        			rs.getString("FullName"), rs.getDate("DateComplete"), rs.getInt("Status"), rs.getDate("DateStart"));
						prPro.setNameManager(rs.getString("FullName"));
						listproject_tc.add(prPro);
					}
			rs.close();
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		finally{
			hep.close();
		}
		return listproject_tc;
	}
	
	public void addNewCommentErrorDAO_tc(String database,String idproject,String idemployee,String comment,String namecolor){
		try{
			hep.open(database);
			DateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm");
			String id = new Date().getTime() + "";
			comment += "-%$#@!-" + namecolor;
			String sql = "insert into pr_comment(IDComment,IDTestProject,IDEmployee,Comment,DateComment) " +
       			 "VALUES(?,?,?,?,?)";
			pr=hep.getConnection().prepareStatement(sql);
			pr.setString(1, id);
            pr.setString(2, idproject);
            pr.setString(3, idemployee);
            pr.setString(4, comment);
            pr.setString(5, format.format(new Date()));
            pr.executeUpdate();
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		finally{
			hep.close();
		}
	}
	
}
